package recommenRatings.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import recommenRatings.utils.*;

import recommenRatings.domain.Opinion;
import recommenRatings.domain.Photo;
import recommenRatings.domain.Place;

public class JDBCOpinionDAO implements IOpinionDAO {

	public void delete(Connection conn, String opinionOID) {
		String sql = "DELETE FROM Opinion WHERE (opinionPOID = ?) ";
		PreparedStatement stmt = null;

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, opinionOID);
			stmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}
	}

	public void insert(Opinion o, String username, Place p) {
		Connection conn = ConnectionManager.getInstance().checkOut();
		JDBCUserDAO userDAO = new JDBCUserDAO();
		JDBCPlaceDAO placeDAO = new JDBCPlaceDAO();
		String userOID = userDAO.selectUserOID(conn, username);
		String placeOID = UIDGenerator.getInstance().getKey();
		placeDAO.insert(conn, p);
		String sql = "INSERT INTO Opinion ( rating, text, opinionPOID, userPOID, placePOID) VALUES (?, ?, ?, ?, ?) ";
		PreparedStatement stmt = null;
		try {
			stmt = conn.prepareStatement(sql);

			stmt.setDouble(1, o.getValoration());
			stmt.setString(2, o.getText());
			stmt.setString(3, UIDGenerator.getInstance().getKey());
			stmt.setString(4, userOID);
			stmt.setString(5, p.getPlaceID());
			stmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
			}
		}

	}

	public List<Opinion> select(Connection conn, String placeID) {

		String sql = "SELECT * FROM Opinion WHERE (placePOID = ? ) ";
		PreparedStatement stmt = null;
		ResultSet result = null;
		List<Opinion> searchResults = new ArrayList<Opinion>();

		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, placeID);

			result = stmt.executeQuery();

			while (result.next()) {
				Opinion o = null;
				o = new Opinion();
				o.setText(result.getString("text"));
				o.setValoration(result.getDouble("rating"));

				searchResults.add(o);

			}

		} catch (SQLException e) {
			System.out.println("Message: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("ErrorCode: " + e.getErrorCode());
		} finally {

			try {
				if (result != null) {
					result.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException e) {
			}
		}

		return searchResults;
	}
}
